The Business Master (3rd Edition)

home *** CD-ROM | disk | FTP | other *** search

/ The Business Master (3rd Edition) / The Business Master (3rd Edition).iso / files / spreotus / 123tech / macrolib.txt < prev next >

Wrap

Text File | 1984-11-24 | 24KB | 612 lines

1-2-3 Macro Tips A Collection of Illustrative Macros The Macro facility (The Typing Alternative) is a powerful tool; it can allow you to get the most out of 1-2-3. However, it can be a little intimidating. The basic concepts are simple. When you use 1-2-3 without Macros, you press keys to enter numbers and labels, to move the cell pointer, and to issue commands. Macros are labels containing just those keystrokes you would normally type into 1-2-3. There are some keys that you can't type directly into a label, like the pointer movement keys and the function keys. In Macros, these are represented by words in braces, like {left} or {graph}. Of course, it can get more complicated. There are special 1-2-3 commands, the /X commands, that can only be used in Macros. These determine which Macro instructions are actually executed; they allow you to write Macros that are computer programs. (If you haven't yet done so, read the 1-2-3 manual section on Macros before going on.) [23;1H[J Key S or <ENTER> to continue[23;30H!s [23;1H[JA good time to use a Macro is when you find yourself typing the same keystrok again and again. Just remember what keys you are striking, and type the same strokes into a label, using the bracketed instructions when necessary. Macros are also problem-solvers. One of the best times to write a Macro is when you are trying to perform some task, and none of the 1-2-3 commands seems to do exactly what you want. Using a Macro, you can create your own commands! To help you get started, we've assembled some examples of Macros that we have found useful. These examples are presented in terms of problems and solutions, because that's the way Macros tend to get developed. However, even if you don't think you will ever face such a problem, it's probably worth reading the example anyway; they can teach you some useful general techniques. We've adopted a Macro-writing style that might be helpful. The Macro name, and other cells with range names in the Macro, are labeled in the column to the left of the Macro. This way, you can assign all the range names at one time, using /Range Name Label Right. Also, we capitalize range names within Macros, and use lower case for the commands. This makes the Macros easier to read. Here are some problems, and their Macro solutions. Editing Labels Problem: To indent some labels. There are times when you've typed a column full of labels, and you want to indent some of them. You could insert a column, and move over the labels to be indented, but this might be easier. Solution: The indent (\I) Macro. Place the cell pointer on the label cell you want indented. press ALT-I. Version 1: \I {edit} Go into Edit Mode. {home} Put cursor at the beginning of Edit line. {right} Move past the Label-Prefix. ~ Type two spaces and [Return] to leave Edit Mode. This demonstrates the utility of using Macros to edit labels. With the {home}, {end}, {right} and {left} notations in your Macro, you can move around on the edit line. {del} and {bs} remove characters. To insert characters, simply type them into the Macro string. If you want to leave Edit Mode include a tilde (~) to represent an [Return]. To use this Macro to indent a series of labels in a column, you must position the cell pointer, press ALT-I, move the cell pointer down, press Alt-I, and so on. We can make the Macro more useful by adding the {down} key. \I {edit}{home} | This is Version 1. {right} ~ | {down} Move the cell pointer to the next label. To use it, simply press ALT-I repeatedly. You can manually skip over labels you don't want to indent. Adding the {down} keystroke to the Macro makes it a lot easier to use. Whenever a Macro will be used repeatedly, on different cells in a particular order, it's a good idea to include arrow keys at the end of the Macro, to move the cell pointer to what will probably be the next cell. In fact, some useful Macros consist of just arrow keys. Movement Macros Problem: To move the cell pointer more than one cell at a time. You can create Macros that move the cell pointer in big jumps. These can be for special purposes, as when working on data where corresponding numbers are six columns apart. Or you can create a set of general purpose big step Macros. Solution: Big step Macros, Left (\L), Right (\R), Up (\U), Down (\D) You could also choose to give these Macros names of keys that form a diamond pattern: Up (\I), Left (\J), Right (\K), and Down (\M). \L {left}{left}{left}{left} \R {right}{right}{right}{right} \U {up}{up}{up}{up} \D {down}{down}{down}{down} Now, to move in bigger steps, just press ALT-L, R, U or D. Here's one last motion-key Macro that lets you use the numeric keypad. Using the Keypad Problem:To use the numeric keypad without having to switch off the Num-Lock to move the cell pointer. The problem with the numeric keypad is that serves two purposes: movement keys and numbers. If you want to use the keypad as numbers, you can use the [Shift] key to temporarily turn them back into arrows. This Macro, however, moves the cell pointer each time you enter a number, so you don't have to use any keys except the number and [Return] keys. Solution: The move Macro (\M). Use this when you want to enter a column or row of numbers using the keypad. This example moves the cell pointer down after you press [Return]. Use the {?} to make the Macro stop to get input before moving down. \M {?} Wait for input; resume when [Return] is pressed. {down} Move down. After you press ALT-M, 1-2-3 will wait until you press [Return] before continuing with the execution of the Macro. You can tell that you are in a Macro by the little CMD indicator next to the Mode Indicator (upper right corner of the screen). And you can tell that it's waiting for input because the Mode is READY. So far, this isn't very useful. It will move the cell pointer down, but to use it again, you must press ALT-M, which is about as much trouble as Shift-[Down arrow]. What we can do is make the Macro "loop," repeat itself. To do this, use the /XG command, which tells the Macro where to go to get its next instruction. Note that this is different from the {goto} function keystroke, which moves the cell pointer, but does not affect the steps in the execution of the Macro. Place the cell pointer at the top of the column in which you want to type numbers. Press Alt-M and press the Num Lock key (or vice versa). Type the numbers you want to input, and press [Return] after each entry; the cell pointer will move down automatically. When you are finished, press Ctrl-Break to stop the Macro (and turn off the [Num Lock] so you can use the arrow keys). This Macro was discovered by Rich Landsman at Lotus. \M {?} | This is version 1. {down} | /xg\M~ Go back to the beginning of the Macro. This is an "infinite" loop; it keeps going around in circles until you stop it. After you have finished putting your numbers in the column, press Ctrl-Break to stop the Macro. Later we will see how to get a Macro loop to stop itself, but for now, let's look at a different type of problem. Putting a Value in a Cell Problem: To record the date of the last update. Many people have found the @today function and the related Date formats to be a useful way of dating printed material. However, when you use @today to put the date on a worksheet, and save it, the date will change when you read in the worksheet and recalculate it. This is fine for some purposes, but doesn't give you a secure record of the last day a worksheet was revised. Solution: The Update (\U) Macro. Name the cell in which you want the date "DATE," and give it a date format. After you have made your revisions, press ALT-U, and then save the file. Version 1: \U {goto}DATE~ Go to the date cell. @today Type in the function. {calc}~ Replace the function with its present value. This works, but it has one problem. It takes you from wherever you are on the worksheet, and leaves you at "DATE." Version 2: \U /rncHERE~ Give the name "HERE" ~ to the current cell pointer cell. {goto}DATE~ | @today | This is Version 1. {calc}~ | {goto}HERE~ Return to your original position. /rndHERE~ Delete the name. Delete the name at the end of the Macro so that when you use it again, you don't end up the last place it was created. Remember: when you try to create a named range for a name that already exists, the cell pointer goes back to the named range's last position. You could insert a {bs} to bring it back to the position when the Macro was invoked: /rncHERE~{bs}~, but DON'T. If HERE's old position was referred to in a formula (e.g. +C5+HERE+E5), then after you moved it, the formula would include HERE, meaning its new position. D5 the name HERE, the formula automatically became +C5+HERE+E5. And it would stay that way, wherever you put HERE, until you /Range Name Delete HERE. Later on, we'll demonstrate Macros that depend heavily on range names, names that are applied to first one cell, then another. If you don't delete the range name before applying the old range name to a new range, you can end up with all your formulas and range names referring to the same cell. A good general rule is: whenever you create a range name within a Macro, delete it before you exit, or before you change the location(s) to be referred to by the name. Okay, one more version. We don't really need it, but Version 2 does fail if you've created another range named HERE. Anyway, this is a chance to introduce a nice technique (spelled "trick") that one of the Lotus staff discovered. \U /dfDATE~@today~~~ Put the value of today's date in the cell named "DATE." That's it! For this gem, we can all thank Bill Liles, of Product Development (this isn't the kind of development we expected, but don't get in the way of the freight train of creativity). Bill found that you could use the /Data Fill command to plug values into cells. In fact, in the next example, we'll see how it can be used to increment, decrement or otherwise operate on the value currently in a cell. Let's string that same Macro (Version 3) down a column so we can more easily explain what is happening. \U /df Execute the /Data Fill command. DATE~ on the range (one cell) named DATE. @today~ Make the Start value @today. ~~ Accept the defaults for Step and Stop (they won't be used in a one-cell range). What is now in DATE is the VALUE of @today, not the function. The next example will show how to use this technique to count how many times you loop, and stop you when you're done. It also demonstrates how to use range names to find out something about the current cell. Stopping a Loop Problem: To make some cells blank. When 1-2-3 evaluates a formula it treats empty cells as having a value a value of zero. This can create confusion when you have missing data. You can substitute @NA for missing cells, but it doesn't look very neat. Here is a listing of salaries for two years. The user wanted to create a column with the percent change in salary, but some people weren't employed at this company for both years. For them, he wanted just blank cells. Salary 81 Salary 82 $15,000 $16,500 $36,000 $13,000 $14,500 $35,000 $19,500 $21,000 Solution: A Macro and a formula. A standard formula for percent change in salary would be: (Sal82-Sal81)/Sal81 But this gives a value of @ERR when Salary 81 is missing, and a value of -1 when Salary 82 is missing. Let's embed our standard formula in an @if function formula. @IF((Sal81=0)#OR#(Sal82=0),999,(Sal82-Sal81)/Sal81) This takes on a value of 999 when either figure is missing; otherwise it gives the correct value. Here is how the figures look now. Salary 81 Salary 82 Percent change $15,000 $16,500 10.00% $36,000 99900.00% $13,000 $14,500 11.54% $35,000 99900.00% $19,500 $21,000 7.69% Version 1: Next, place the cell pointer at the top of the "Percent Change" column and press ALT-B. Repeat this until the entire column is cleaned up. \B /rncHERE~ Create the one-cell range, HERE. ~ at the present position. /xi(HERE=999)~/re~ If HERE equals 999, erase the cell. /rndHERE~ Delete the cell name (!) {down} And move down. And this is the result: Salary 81 Salary 82 Percent change $15,000 $16,500 10.00% $36,000 $13,000 $14,500 11.54% $35,000 $19,500 $21,000 7.69% Finally, we can put the above Macro in a loop that stops itself. To do this, we will create ahead of time two one- cell named ranges, NCELLS and CNUMBER. First, we'll count the number of cells in the column, and put the number in NCELLS. Then, each time we check (and perhaps erase) a cell, we'll add one to CNUMBER. When CNUMBER is greater than NCELLS, we're finished. Version 2: \B /rncHERE~ Create a range named HERE. {end}{down}~ Make it the whole column. /dfNCELLS~ Put in NCELLS the @count(HERE)~~~ number of cells in the column. /rndHERE~ Delete HERE. /dfCNUMBER~0~~~ Start counting cells with 0. LOOP /rncHERE~ | ]<-- This cell is named "LOOP." ~ | /xi(HERE=999)~/re~ | This is Version 1. /rndHERE~ | {down} | /dfCNUMBER~ Increase the number in CNUMBER CNUMBER+1~~~ by one (increment). /xi(CNUMBER<=NCELLS)~/xgLOOP~ This last line has the Macro resume execution at the cell named LOOP, until the number in CNUMBER is equal to the number in NCELLS. One last example. This one demonstrates how you can turn a number into a formula, and back to a number, using {Edit}. Accumulating Numbers Problem: To Update a value. The user was maintaining a database of sales people and their current monthly sales and sales year-to-date. He wanted to be able to enter the monthly figures and update the year-to- date. Salesperson Year-to-date Current DiAngelo $83,000 $6,000 Gottfried $56,000 $8,000 Jones $48,000 $5,000 Washington $77,000 $9,000 These were the figures after the previous month's entries. They are numbers, not formulas. Solution: The accumulate Macro (\A). This Macro takes advantage of the fact that while editing a formula, you can point to cells, just as when you are first entering formulas. You must be at the end of the edit line and the last character must be one that could be followed by a cell or range such as an operator (e.g. +, -, #AND#) or an open parenthesis. To start pointing to cells, rather than move along the edit line, press the {edit} key again to put you in VALUE mode. When you then press the pointer-movement key you will be in POINT mode, as when entering a formula. Place the cell pointer on the first "current" cell. Press ALT-A. Type the new current value and press [Return]. Press ALT-A again to enter the value for the next salesperson. Version 1: \A /re~ Erase the old current value. {?}~ Wait for user to put in new value. {left} Go to the year-to-date cell. {edit} Edit it. + Adding the plus turns it into a formula. {edit} The {edit} key puts you in VALUE Mode. {right}~ Add the value in the "Current" cell. {edit}{calc}~ Turn the formula back into a number. {right}{down} Move to next current cell. Remember that when the Macro is waiting for input ({?}) the CMD indicator is next to the READY Mode indicator. Next, let's put the Macro in a loop, so that it will keep asking for this month's figures, and stop when it reaches the end of the column. The only requirement is that there be no empty cells in the year-to-date column (except at the end), so put zeros in any empty cells. This version uses a different technique to stop at the end of the column; it checks to see if the next cell is blank, and if it is, it stops. Actually, there is a problem finding out if a single cell is blank. Labels, blank cells and zeros all have the value of zero, so you can't use that. And @count always has a value of one if its' argument is a one-cell range. This Macro uses @count and a two-cell range, named TEST. The range consists of the potential next "Current" cell and the one cell above it. The value of @count(TEST) will be 2, until it hits the end of the column, when it will be 1. Version 2: \A /re~ | {?}~ | {left} | {edit} | + | This is Version 1. {edit} | {right}~ | {edit}{calc}~ | {right}{down} | /rncTEST~{up}~ /xi(@count(TEST)=2)~/rndTEST~/xg\A~ /rndTEST~ It's hard to put comments next to those long Macro lines, so let's take the last three lines one at a time: /rncTEST~{up}~ Creates a two-cell range consisting of the potential next "Current" cell and the one above it. /xi(@count(TEST)=2)~/rndTEST~/xg\A~ This checks to see if the value of @count(TEST) is still 2. If it is, it deletes TEST (important!) and tells the Macro to start again from the beginning. This Macro label had to be long, because the /xi command tells the Macro to do the rest of the Macro label, if the condition is true. /rndTEST~ And finally, to clean things up, we delete TEST at the end of the whole Macro. We had to do this because when @count(TEST) is NOT 2, the rest of that line doesn't get executed, and TEST still exists. Well, that should be enough to help you get started with Macros. Give them a try, and write some of your own.